#__author__ = 'Ion Morozan'
#Profile data manager

import MySQLdb
import profile
import warnings

warnings.filterwarnings("ignore", "Unknown table.*")

#database info
HOST = 'localhost'
USER = 'root'
PASSWD = 'root'
DB = 'OFFLINE_PROFILING'
TABLE = 'profile'

class ProfileDataManager:

#    def __init__(self):

    #create database OFFLINE_PROFILING and drops it if already exists
    def createDatabase(self):
        db = MySQLdb.connect(host= HOST,        #host, usually localhost
            user= USER,        #username
            passwd= PASSWD)    #password(password depends on local settings)

        # create a Cursor object. It will let execute all the query we need
        cur = db.cursor()

        #create database
        cur.execute('DROP DATABASE IF EXISTS {0}'.format(DB))
        cur.execute('CREATE DATABASE {0}'.format(DB))

        # Close all cursors
        cur.close()
        # Close all databases
        db.close()

    # connect to an existing database
    def connectDatabase(self):
        db = MySQLdb.connect( host= HOST,       #host, usually localhost
            user= USER,       #username
            passwd= PASSWD,   #password
            db = DB);         #select database

        return db

    #create table profile and drops it if already exists
    def createTable(self):
        # connect to appropriate database
        db = self.connectDatabase()

        # create a Cursor object. It will let execute all the query we need
        cur = db.cursor()

        # remove table if exists
        cur.execute('DROP TABLE IF EXISTS {0}'.format(TABLE))

        # create table
        cur.execute('''
                        CREATE TABLE {0}
                        (
                            ip              VARCHAR(15) NOT NULL PRIMARY KEY,

                            min_req_rate    FLOAT NOT NULL,
                            max_req_rate    FLOAT NOT NULL,

                            min_cpu_usage   FLOAT NOT NULL,
                            max_cpu_usage   FLOAT NOT NULL,

                            vm_instance     TEXT NOT NULL,
                            nr_cpus         FLOAT NOT NULL,
                            memory          FLOAT NOT NULL
                        )
                    '''.format(TABLE))

        # Close all cursors
        cur.close()
        # Close all databases
        db.close()


    # print the content of active TABLE of DB
    def printTable(self):
        rows = []

        # connect to appropriate database
        db = self.connectDatabase()

        # create a Cursor object. It will let execute all the query we need
        cur = db.cursor()

        # Get data from database
        try:
            cur.execute('SELECT * FROM {0}'.format(TABLE))
            rows = cur.fetchall()
        except MySQLdb.Error, e:
            try:
                print 'MySQL Error [{0}]: {1}]'.format(e.args[0], e.args[1])
            except IndexError:
                print 'MySQL Error: {%0}'.format(str(e))

        # Print results in comma delimited format
        for row in rows:
        #            for col in row:
            print  row
            #            print '\n'

    #add entry to TABLE
    def add(self, IP, min_req_rate, max_req_rate, min_cpu_usage, max_cpu_usage, vm_instance, nr_cpus, memory):

        # connect to appropriate database
        db = self.connectDatabase()

        # create a Cursor object. It will let execute all the query we need
        cur = db.cursor()

        try:
            #add data to TABLE
            cur.execute('''
                        INSERT INTO {0}
                        (ip,min_req_rate, max_req_rate, min_cpu_usage, max_cpu_usage, vm_instance, nr_cpus, memory)
                        VALUES
                        (%s, %s, %s, %s, %s, %s, %s, %s)
                    '''
            .format(TABLE), (IP, min_req_rate, max_req_rate, min_cpu_usage, max_cpu_usage, vm_instance, nr_cpus, memory)
            )
        except MySQLdb.Error, e:
            try:
                print 'MySQL Error [{0}]: {1}]'.format(e.args[0], e.args[1])
            except IndexError:
                print 'MySQL Error: {%0}'.format(str(e))


        #commit data
        db.commit()
        # Close all cursors
        cur.close()
        # Close all databases
        db.close()

    #delete entry from TABLE based on PK
    def delete(self, IP):

        # connect to appropriate database
        db = self.connectDatabase()

        # create a Cursor object. It will let execute all the query we need
        cur = db.cursor()

        try:
            #delete data from TABLE based on PK
            cur.execute('''

                        DELETE FROM {0} WHERE ip = \'{1}\'

                    '''.format(TABLE, IP))

        except MySQLdb.Error, e:
            try:
                print 'MySQL Error [{0}]: {1}]'.format(e.args[0], e.args[1])
            except IndexError:
                print 'MySQL Error: {%0}'.format(str(e))

        #commit data
        db.commit()
        # Close all cursors
        cur.close()
        # Close all databases
        db.close()

    #update entry from TABLE based on PK and constraints
    def update(self, IP, min_req_rate, max_req_rate, min_cpu_usage, max_cpu_usage, vm_instance, nr_cpus, memory):

        # connect to appropriate database
        db = self.connectDatabase()

        # create a Cursor object. It will let execute all the query we need
        cur = db.cursor()

        try:
            #update an entry from  TABLE
            cur.execute('''

                        UPDATE {0}
                        SET min_req_rate = %s,
                            max_req_rate = %s,
                            min_cpu_usage = %s,
                            max_cpu_usage = %s ,
                            vm_instance = %s,
                            nr_cpus = %s ,
                            memory = %s
                        WHERE ip = \'{1}\'

                    '''.
            format(TABLE, IP), (min_req_rate, max_req_rate, min_cpu_usage, max_cpu_usage, vm_instance, nr_cpus, memory))

        except MySQLdb.Error, e:
            try:
                print 'MySQL Error [{0}]: {1}]'.format(e.args[0], e.args[1])
            except IndexError:
                print 'MySQL Error: {%0}'.format(str(e))

        #commit data
        db.commit()
        # Close all cursors
        cur.close()
        # Close all databases
        db.close()
